package com.manager.service.file;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;

import org.jorm.Session;

import com.build.SessionBuilder;
import com.manager.service.ServiceUtil;
import com.web.model.File;
import com.web.model.dao.FileDao;
import com.web.model.dao.impl.FileDaoImpl;
import com.web.model.vo.query.FileQuery;
import com.web.tag.bean.PageModel;

public class FileService {

	public List<File> findListByCat(int tempM, PageModel model) {
		List<File> list = null;
		try {
			Session session = SessionBuilder.oracleSession();
			FileDao fileDao = new FileDaoImpl();
			String whereSql = " where  " + File.COLUMN_CID + "=" + tempM;
			String sql = "select " + File.COLUMN_FID + "," + File.COLUMN_TITLE + "," + File.COLUMN_ADDTIME + "," + File.COLUMN_FILEPATH + "," + File.COLUMN_CID
					+ ",(select c.NAME from TW_CATEGORY c where c.ID = " + File.COLUMN_CID + ") as cname," + File.COLUMN_INFO + "," + File.COLUMN_NUMS + " from " + File.TABLE_NAME
					+ " " + whereSql + " order by " + File.COLUMN_ADDTIME + " desc";

			if (model != null) {
				try {
					model.setRecodeZong(fileDao.getResultCount("select count(*) as ct from " + File.TABLE_NAME + " " + whereSql, 1).intValue());
				} catch (SQLException e) {
					e.printStackTrace();
				}
				// sql = sql + "limit " + model.getCountPage() *
				// (model.getPageNum() - 1) + "," + model.getCountPage();
				int row_x = model.getCountPage() * model.getPageNum();
				int row_y = (model.getPageNum() - 1) * model.getCountPage();
				sql = "select * from ( select row_.*, rownum rownum_ from (" + sql + ") row_ where rownum <= " + row_x + ") where rownum_ > " + row_y;
			}
			list = fileDao.queryForList(sql);
			session.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

	public List<File> findList(PageModel model, FileQuery query) {
		List<File> list = null;
		try {
			Session session = SessionBuilder.oracleSession();
			FileDao fileDao = new FileDaoImpl();
			String whereSql = " where 1=1 ";
			if (query.getCid() != null && query.getCid() > 0) {
				whereSql += " and " + File.COLUMN_CID + " = " + query.getCid();
			}
			if (query.getTitle() != null && !query.getTitle().trim().isEmpty()) {
				whereSql += " and " + File.COLUMN_TITLE + " like '%" + query.getTitle() + "%'";
			}
			String sql = "select " + File.COLUMN_FID + "," + File.COLUMN_TITLE + "," + File.COLUMN_ADDTIME + "," + File.COLUMN_FILEPATH + "," + File.COLUMN_CID
					+ ",(select c.NAME from TW_CATEGORY c where c.ID = " + File.COLUMN_CID + ") as cname from " + File.TABLE_NAME + " " + whereSql + " order by "
					+ File.COLUMN_ADDTIME + " desc";

			if (model != null) {
				try {
					model.setRecodeZong(fileDao.getResultCount("select count(*) as ct from " + File.TABLE_NAME + " " + whereSql, 1).intValue());
				} catch (SQLException e) {
					e.printStackTrace();
				}
				// sql = sql + "limit " + model.getCountPage() *
				// (model.getPageNum() - 1) + "," + model.getCountPage();
				int row_x = model.getCountPage() * model.getPageNum();
				int row_y = (model.getPageNum() - 1) * model.getCountPage();
				sql = "select * from ( select row_.*, rownum rownum_ from (" + sql + ") row_ where rownum <= " + row_x + ") where rownum_ > " + row_y;
			}
			list = fileDao.queryForList(sql);
			session.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		return list;
	}

	public void saveFile(File file) {
		try {
			Session session = SessionBuilder.oracleSession();
			int fid = new ServiceUtil(session).getNextId(File.TABLE_NAME, File.COLUMN_FID);
			String sql = "insert into " + File.TABLE_NAME + " (FID,TITLE,FILEPATH,ADDTIME,INFO,CID,NUMS) values(?,?,?,sysdate,?,?,0)";
			Connection conn = session.connection();
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, fid);
			ps.setString(2, file.getTitle());
			ps.setString(3, file.getFilepath());
			ps.setString(4, file.getInfo());
			ps.setInt(5, file.getCid());
			ps.executeUpdate();
			ps.close();
			session.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void deleteFile(int fid) {
		try {
			Session session = SessionBuilder.oracleSession();
			String sql = "delete from " + File.TABLE_NAME + " where " + File.COLUMN_FID + " = ?";
			Connection conn = session.connection();
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, fid);
			ps.executeUpdate();
			ps.close();
			session.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

}
